clear
set more off

/*THIS PROGRAM CONSTRUCTS FINAL DATA SET */ 

/*FIRST MERGE HOUSEHOLD INTERVIEWS WITH COMMUNITY LEVEL FILE WITH TREATMENT VARIABLE*/

*process individual data on all in the household*

use "$data/01_dataprep/2. CICLO FAMILIA PROSPERA HIDALGO.dta" 
*gen hh size and characteristics of the titular*
gen count=1
egen hhsize=sum(count), by (FOLIO)
gen titular=1 if P_3==2 | P_3==3
gen age=P_4
replace age=. if P_4==99
gen schooling=P_8
gen attends_school=(P_9_b==1)
gen female=(P_5==2)
gen married=(P_6==1 | P_6==4)
keep if titular==1
gen folio=real(FOLIO)
drop FOLIO
rename folio FOLIO
sort FOLIO
save "$tempdata/hhroster.dta", replace

/*
/* eliminate 50% of oversampled communties*/

use "$Data/1. BASE PROSPERA HIDALGO.dta" 
sample 50 if ID_LOC=="130280013" 
sample 50 if ID_LOC=="130280045"
sample 50 if ID_LOC=="130280085"
sort ID_LOC
save "$Data/BASE PROSPERA HIDALGO_4410obs.dta", replace 
*/

use "$data/01_dataprep/BASE PROSPERA HIDALGO_4410obs.dta", replace 
sort FOLIO
merge FOLIO using "$tempdata/hhroster.dta" 
keep if _merge==3
drop _merge
sort ID_LOC
save "$tempdata/BASE PROSPERA HIDALGO_all.dta", replace 

/* this data merges treatment status to outcome data*/

use "$data/01_dataprep/Community_Hidalgo.dta"
drop _merge
rename CVE ID_LOC
sort ID_LOC
merge ID_LOC using "$tempdata/BASE PROSPERA HIDALGO_all.dta" 
keep if _merge==3

*all merge except for 3 communities where fieldwork couldn't be carried out-2 control and 1 treatment1*
* added extra 30 interviews in 3 communities, randomly select 30 in these and delete others*

*data cleaning*

foreach var of varlist P_31 P_32 P_104* {
replace `var'=. if `var'==9999
}

foreach var of varlist P_42 P_60 P_61 P_62 P_63 P_50 {
replace `var'=. if `var'==99
}

mvdecode P_66* P_21 P_31 P_32 P_33 P_34 P_35 P_36*, mv(99)
mvdecode P_53*, mv(9999)

foreach var of varlist P_88 P_105_a P_105_b P_105_c P_105_d P_105_e P_107 P_112 P_117* P_111 P_125 {
replace `var'=. if `var'==99
replace `var'=. if `var'==999
replace `var'=. if `var'==999999
}

gen treat1=0
replace treat1=1 if trat==1

gen treat2=0
replace treat2=1 if trat==2


*Other control variables*

gen cuartos=P_94
gen wood_cooking=1 if P_102==1
replace wood_cooking=0 if P_102==3
gen radio=0
replace radio=1 if P_103_d==1
gen refri=1 if P_103_n==1
replace refri=0 if P_103_n==0
gen tele=1 if P_103_o==1
replace tele=0 if P_103_o==0
gen licuador=1 if P_103_u==1
replace licuador=0 if P_103_u==0
gen lavadora=1 if P_103_v==1
replace lavadora=0 if P_103_v==0

*Work related variables*

gen works_lwk=P_42
gen hours=P_50
replace hours=0 if P_50==.
gen self=0
replace self=1 if P_43==2
egen labincome=rsum(P_53*)

*Spouse contributes to household*

gen hasspouse=P_60
gen spouse_inhh=P_61
gen spouse_works=P_62
gen spouse_contrib=P_63

*savings, financess*

gen account_savings=1 if P_107==1
replace account_savings=0 if P_107==0

gen pct_savings=P_125 if P_125~=999

gen track_expend=(P_88==1 | P_88==2)

rename P_105_a savings_family
rename P_105_b savings_caja
rename P_105_c loaned
rename P_105_d savings_house
rename P_111 useATM

* household decision making*
*create only women decides variables*

foreach var of varlist P_66_a P_66_b P_66_c P_66_d P_66_e P_66_f P_66_g P_66_h P_66_i P_66_j P_66_k P_66_l P_66_m P_66_n{
gen wom`var'=(`var'==1) 
*gen both`var'=(`var'==5)*
}

*create only women decides variables*

foreach var of varlist P_67_a P_67_b P_67_c P_67_d P_67_e P_67_f P_67_g {
gen wom`var'=(`var'==1) 
*gen both`var'=(`var'==5)*
}

gen escuchado_cambios=(P_152==1)

save "$tempdata/FINAL_DATASET.dta", replace

clear

use "$tempdata/FINAL_DATASET.dta", clear

*Step 1 in analysis:  check program was implemented*

tab P_19 trat 
*tab P_11 trat // muni nacimiento

local i=1
foreach var in diconsa atm_ban bansefi atm_otro map other bankbranch cashier dkna {
gen `var'=0
replace `var'=(P_19==`i')
local i=`i'+1
di `i'	
}

gen total=1

preserve
	collapse (sum) diconsa atm_ban bansefi atm_otro map other bankbranch cashier dkna total, by(trat)
	 label var map "Temp. Payment Point (MAP)"
	 label var atm_otro "ATM"
	 label var atm_ban "ATM (Bansefi)"
	 label var cashier "Cashier"
	 label var bansefi "Bansefi branch"
	 label var bankbranch "Bank branch"
	 label var diconsa "Diconsa store"
	 label var other "Other"
	 label var dkna "Don't know/NA"
	 label var total "\textbf{Total}"

	eststo clear	
	eststo t0: estpost summarize map atm_otro atm_ban cashier bansefi bankbranch diconsa other dkna total if trat==0
	eststo t1: estpost summarize map atm_otro atm_ban cashier bansefi bankbranch diconsa other dkna total if trat==1
	eststo t2: estpost summarize map atm_otro atm_ban cashier bansefi bankbranch diconsa other dkna total if trat==2
	
/* Table 1*/
	
	esttab t0 t1 t2 using "$results/tables/3_T_SummStatsTREAT.tex", replace fragment booktabs mgroups("Control" "T1 (no SMS)"  "T2 (w/SMS)", pattern(1 1 1 ) prefix(\multicolumn{@span}{c}{) suffix(}) span erepeat(\cmidrule(lr){@span})) cells(mean(pattern(1 1 1) fmt(0) label("" "" ""))) label nomtitle nonumber noobs  
	
restore


*time and money to pick up transfers*
* P_30 received_full P_31 pesos P_32 minutes P_33 hard_commute P_34 unsafe_commute P_35 far_commute
foreach var of varlist P_30 P_31 P_32{
ttest `var', by (trat1) 
ttest `var', by (trat2)
}


*Do program officials ask for money or gifts in exchange*
* P_36_a P_36_b P_36_c P_36_d P_36_e
foreach var of varlist P_36_a P_36_b P_36_c P_36_d P_36_e {
ttest `var', by (trat1) 
ttest `var', by (trat2)
}


*Step 2:  check randomization*
*combine two treatment groups for now*
*at community and individual level*
*present community level randomization tests*

* P_30 received_full P_31 pesos P_32 minutes P_33 hard_commute P_34 unsafe_commute P_35 far_commute
	label var P_30 "Received full CCT" 
	label var P_31 "Money (\$, two-way)" 
	label var P_32 "Time (min., one-way)" 
	label var P_33 "\% reporting difficult" 
	label var P_34 "\% reporting unsafe" 
	label var P_35 "\% reporting far"

	label var P_36_a "Attend political events" 
	label var P_36_b "Do community work" 
	label var P_36_c "Food or Gifts" 
	label var P_36_d "Money" 
	label var P_36_e "Vote for party"

label var married "Married" 
label var spouse_inhh "Spouse lives in HH" 
label var spouse_works "Spouse works" 
label var spouse_contrib "Spouse contributesto HH"
	

collapse (mean) treat1 treat2 trat1 trat2 N_ATM_5km prop_cel prop_piso_tierra prop_refri prop_agua_dentro prop_vph_pc altitud pobtot prop_hablaindig age married trat attends_school cuartos wood_cooking radio tele lavadora  P_36_a P_36_b P_36_c P_36_d P_36_e P_30 P_31 P_32 P_33 P_34 P_35, by (CVE_INEGI)

rename N_ATM_5km N_ATM
rename prop_agua_dentro prop_agua
rename prop_piso_tierra prop_piso
gen treatment=trat
replace treatment=1 if trat==2

label var N_ATM "No. ATMs"
label var prop_cel "\% Cel"
label var prop_piso "\% Dirt FLoor"
label var prop_refri "\% Fridge"
label var prop_agua "\% Water"
label var prop_vph_pc "\% Computer"
label var altitud "Altitude"
label var pobtot "Tot. Pop."
label var prop_hablaindig "\% Indig."
label var age "Age"
label var married "Married"
label var attends_school "Attends School"

/*Balance check, Table 2*/
* Muni characteristics
* C vs T1 and T2
************
eststo clear
************
eststo control: estpost summarize N_ATM prop_cel prop_piso prop_refri prop_agua prop_vph_pc altitud pobtot prop_hablaindig age married attends_school if trat1==0
eststo treat1: estpost summarize N_ATM prop_cel prop_piso prop_refri prop_agua prop_vph_pc altitud pobtot prop_hablaindig age married attends_school if trat1==1
eststo treat2: estpost summarize N_ATM prop_cel prop_piso prop_refri prop_agua prop_vph_pc altitud pobtot prop_hablaindig age married attends_school if trat2==1
eststo diff1: estpost ttest N_ATM prop_cel prop_piso prop_refri prop_agua prop_vph_pc altitud pobtot prop_hablaindig age married attends_school, by(trat1)
eststo diff2: estpost ttest N_ATM prop_cel prop_piso prop_refri prop_agua prop_vph_pc altitud pobtot prop_hablaindig age married attends_school, by(trat2)

esttab control treat1 treat2 diff1 diff2 using "$results/tables/2_T_balance_all.tex", replace fragment booktabs ///
starl(* 0.1 ** 0.05 *** 0.01) mgroups("Control" "T1" "T2" "C vs T1" "C vs T2" , pattern(1 1 1 1 1) prefix(\multicolumn{@span}{c}{) suffix(}) span erepeat(\cmidrule(lr){@span})) ///
cells("mean(pattern(1 1 1 0 0) fmt(2) label(Mean)) sd(pattern(1 1 1 0 0) label(Std. Dev.)) b(star pattern(0 0 0 1 1) fmt(2) label(Diff)) t(pattern(0 0 0 1 1) par fmt(2) label(t-test))") ///
label nonumber stats(N, label("Observations") fmt(%12.0fc))


* T1 vs C
************
eststo clear
************
eststo control: estpost summarize N_ATM prop_cel prop_piso prop_refri prop_agua prop_vph_pc altitud pobtot prop_hablaindig age married attends_school if trat1==0
eststo treat: estpost summarize N_ATM prop_cel prop_piso prop_refri prop_agua prop_vph_pc altitud pobtot prop_hablaindig age married attends_school if trat1==1
eststo diff: estpost ttest N_ATM prop_cel prop_piso prop_refri prop_agua prop_vph_pc altitud pobtot prop_hablaindig age married attends_school, by(trat1)

esttab control treat diff using "$results/tables/2_T_balance1.tex", replace fragment booktabs ///
starl(* 0.1 ** 0.05 *** 0.01) mgroups("Control" "T1"  "" , pattern(1 1 1 ) prefix(\multicolumn{@span}{c}{) suffix(}) span erepeat(\cmidrule(lr){@span})) ///
cells("mean(pattern(1 1 0) fmt(2) label(Mean)) sd(pattern(1 1 0) label(Std. Dev.)) b(star pattern(0 0 1) fmt(2) label(Diff)) t(pattern(0 0 1) par fmt(2) label(t-test))") ///
label nonumber stats(N, label("Observations") fmt(%12.0fc))


* T2 vs C
************
eststo clear
************
eststo control: estpost summarize N_ATM prop_cel prop_piso prop_refri prop_agua prop_vph_pc altitud pobtot prop_hablaindig age married attends_school if trat2==0
eststo treat: estpost summarize N_ATM prop_cel prop_piso prop_refri prop_agua prop_vph_pc altitud pobtot prop_hablaindig age married attends_school if trat2==1
eststo diff: estpost ttest N_ATM prop_cel prop_piso prop_refri prop_agua prop_vph_pc altitud pobtot prop_hablaindig age married attends_school, by(trat2)

esttab control treat diff using "$results/tables/2_T_balance2.tex", replace fragment booktabs ///
starl(* 0.1 ** 0.05 *** 0.01) mgroups("Control" "T2"  "" , pattern(1 1 1 ) prefix(\multicolumn{@span}{c}{) suffix(}) span erepeat(\cmidrule(lr){@span})) ///
cells("mean(pattern(1 1 0) fmt(2) label(Mean)) sd(pattern(1 1 0) label(Std. Dev.)) b(star pattern(0 0 1) fmt(2) label(Diff)) t(pattern(0 0 1) par fmt(2) label(t-test))") ///
label nonumber stats(N, label("Observations") fmt(%12.0fc))

gen any_treatment=(treat1==1 | treat2==1)
label var any_treatment "Any treatment"
/*
gen notified=(P_23==1)
	replace notified=. if P_23==99
label var notified "Notified"

* Table pre: Impact on ease of collecting CCT

************
eststo clear
************

foreach var of varlist notified P_24 P_28 P_29 P_30 P_31 P_32 P_34 {
	eststo t1t2_`var': regress `var' treat1 treat2 $control1 $control2, cluster(CVE_INEGI)
	estadd ysumm, mean

	eststo anyt_`var': regress `var' any_treatment $control1 $control2, cluster(CVE_INEGI)
	estadd ysumm, mean
}	

esttab t1t2_notified t1t2_P_24 t1t2_P_28 t1t2_P_29 t1t2_P_30 t1t2_P_31 t1t2_P_32 t1t2_P_34 ///
using "$results/tables/TT_CCTimpact.tex", replace fragment booktabs b(%12.3fc) se(%12.3fc) se keep (treat1 treat2)   ///
starl(* 0.1 ** 0.05 *** 0.01)  ///
noobs label varwidth(22) wrap


esttab anyt_notified anyt_P_24 anyt_P_28 anyt_P_29 anyt_P_30 anyt_P_31 anyt_P_32 anyt_P_34 ///
using "$results/tables/TT_CCTimpact.tex", append fragment booktabs b(%12.3fc) se(%12.3fc) se keep (any_treatment) ///
starl(* 0.1 ** 0.05 *** 0.01) varlabels(any_treatment "Any treatment")  ///
nomtitles nonumbers stats(ymean N, label("Mean dept. var." "N") fmt(%12.2fc %12.0fc))  ///
label varwidth(22) wrap

